ALTER TABLE person ADD COLUMN middle_name character varying(25);
ALTER TABLE person ADD COLUMN id_employee integer;

DROP FUNCTION sp_person_count(integer, boolean, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, timestamp without time zone, timestamp without time zone, character varying, boolean, character varying, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_person_insert(boolean, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, timestamp without time zone, character varying, boolean, character varying, integer, boolean, integer, integer);
DROP FUNCTION sp_person_selectlist(integer, integer, integer, boolean, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, timestamp without time zone, timestamp without time zone, character varying, boolean, character varying, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_person_update(integer, boolean, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, timestamp without time zone, character varying, boolean, character varying, integer, boolean, integer, integer, boolean, timestamp without time zone);

CREATE OR REPLACE FUNCTION sp_person_insert(
	_is_employee	boolean DEFAULT null,
	_first_name	character varying(25) DEFAULT null,
	_last_name	character varying(25) DEFAULT null,
	_mobile	character varying(20) DEFAULT null,
	_company_phone	character varying(25) DEFAULT null,
	_home_phone	character varying(25) DEFAULT null,
	_company_email	character varying(255) DEFAULT null,
	_personal_email	character varying(255) DEFAULT null,
	_address	character varying(255) DEFAULT null,
	_birthdate	timestamp without time zone DEFAULT null,
	_identity_no	character varying(25) DEFAULT null,
	_gender	boolean DEFAULT null,
	_description	character varying DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_creator	integer DEFAULT null,
	_middle_name	character varying(25) DEFAULT null,
	_id_employee	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO person(is_employee,first_name,last_name,mobile,company_phone,home_phone,company_email,personal_email,address,birthdate,identity_no,gender,description,status,is_draft,notification,creator,updator,deletion_time,middle_name,id_employee)
	VALUES(_is_employee,_first_name,_last_name,_mobile,_company_phone,_home_phone,_company_email,_personal_email,_address,_birthdate,_identity_no,_gender,_description,_status,_is_draft,_notification,_creator,_creator,null,_middle_name,_id_employee);
RETURN currval('person_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_person_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_is_employee	boolean DEFAULT null,
	_first_name	character varying(25) DEFAULT null,
	_last_name	character varying(25) DEFAULT null,
	_mobile	character varying(20) DEFAULT null,
	_company_phone	character varying(25) DEFAULT null,
	_home_phone	character varying(25) DEFAULT null,
	_company_email	character varying(255) DEFAULT null,
	_personal_email	character varying(255) DEFAULT null,
	_address	character varying(255) DEFAULT null,
	_birthdateFrom	timestamp without time zone DEFAULT null,
	_birthdateTo	timestamp without time zone DEFAULT null,
	_identity_no	character varying(25) DEFAULT null,
	_gender	boolean DEFAULT null,
	_description	character varying DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_middle_name	character varying(25) DEFAULT null,
	_id_employee	integer DEFAULT null
)
RETURNS SETOF person
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM person WHERE
	 	(_id is null OR id = _id)	
	AND (_is_employee is null OR is_employee = _is_employee)	
	AND (_first_name is null OR first_name = _first_name)	
	AND (_last_name is null OR last_name = _last_name)	
	AND (_mobile is null OR mobile = _mobile)	
	AND (_company_phone is null OR company_phone = _company_phone)	
	AND (_home_phone is null OR home_phone = _home_phone)	
	AND (_company_email is null OR company_email = _company_email)	
	AND (_personal_email is null OR personal_email = _personal_email)	
	AND (_address is null OR address = _address)	
	AND (_birthdateFrom is null OR birthdate >= _birthdateFrom)
	AND	(_birthdateTo is null OR birthdate <= _birthdateTo)	
	AND (_identity_no is null OR identity_no = _identity_no)	
	AND (_gender is null OR gender = _gender)	
	AND (_description is null OR description = _description)	
	AND (_status is null OR status = _status)	
	AND (_is_draft is null OR is_draft = _is_draft)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_middle_name is null OR middle_name = _middle_name)	
	AND (_id_employee is null OR id_employee = _id_employee)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_person_count (
	_id	integer DEFAULT null,
	_is_employee	boolean DEFAULT null,
	_first_name	character varying(25) DEFAULT null,
	_last_name	character varying(25) DEFAULT null,
	_mobile	character varying(20) DEFAULT null,
	_company_phone	character varying(25) DEFAULT null,
	_home_phone	character varying(25) DEFAULT null,
	_company_email	character varying(255) DEFAULT null,
	_personal_email	character varying(255) DEFAULT null,
	_address	character varying(255) DEFAULT null,
	_birthdateFrom	timestamp without time zone DEFAULT null,
	_birthdateTo	timestamp without time zone DEFAULT null,
	_identity_no	character varying(25) DEFAULT null,
	_gender	boolean DEFAULT null,
	_description	character varying DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_middle_name	character varying(25) DEFAULT null,
	_id_employee	integer DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM person WHERE
	 	(_id is null OR id = _id)	
	AND (_is_employee is null OR is_employee = _is_employee)	
	AND (_first_name is null OR first_name = _first_name)	
	AND (_last_name is null OR last_name = _last_name)	
	AND (_mobile is null OR mobile = _mobile)	
	AND (_company_phone is null OR company_phone = _company_phone)	
	AND (_home_phone is null OR home_phone = _home_phone)	
	AND (_company_email is null OR company_email = _company_email)	
	AND (_personal_email is null OR personal_email = _personal_email)	
	AND (_address is null OR address = _address)	
	AND (_birthdateFrom is null OR birthdate >= _birthdateFrom)
	AND	(_birthdateTo is null OR birthdate <= _birthdateTo)	
	AND (_identity_no is null OR identity_no = _identity_no)	
	AND (_gender is null OR gender = _gender)	
	AND (_description is null OR description = _description)	
	AND (_status is null OR status = _status)	
	AND (_is_draft is null OR is_draft = _is_draft)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_middle_name is null OR middle_name = _middle_name)	
	AND (_id_employee is null OR id_employee = _id_employee);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_person_update 
(	_id integer,
	_is_employee boolean,
	_first_name character varying(25),
	_last_name character varying(25),
	_mobile character varying(20),
	_company_phone character varying(25),
	_home_phone character varying(25),
	_company_email character varying(255),
	_personal_email character varying(255),
	_address character varying(255),
	_birthdate timestamp without time zone,
	_identity_no character varying(25),
	_gender boolean,
	_description character varying,
	_status integer,
	_is_draft boolean,
	_notification integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone,
	_middle_name character varying(25),
	_id_employee integer  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE person 
	SET 		is_employee = _is_employee,
		first_name = _first_name,
		last_name = _last_name,
		mobile = _mobile,
		company_phone = _company_phone,
		home_phone = _home_phone,
		company_email = _company_email,
		personal_email = _personal_email,
		address = _address,
		birthdate = _birthdate,
		identity_no = _identity_no,
		gender = _gender,
		description = _description,
		status = _status,
		is_draft = _is_draft,
		notification = _notification,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time,
		middle_name = _middle_name,
		id_employee = _id_employee
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
